package org.ssp.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import org.ssp.base.ResultSetHanlder;
import org.ssp.dao.UserDao;
import org.ssp.pojo.Task;
import org.ssp.pojo.TimerMessage;
import org.ssp.pojo.Travel;
import org.ssp.pojo.User;
import org.ssp.util.DBUtil;


public class UserImpl implements UserDao {
	//1.查出所有没有助理的用户
		@Override
		public ArrayList<User> findNoassigned(int offset,int rows) {
			String sql="select * from user where assistid is null  limit ?,?";
			return (ArrayList<User>)DBUtil.query(sql, new ResultSetHanlder() {
				@Override
				public Object doHandler(ResultSet rs) {
					ArrayList<User> list=new ArrayList<User>();
					try {
						while(rs.next()){
							User user=new User();
							user.setUid(rs.getString("uid"));
							user.setPhone(rs.getString("phone"));
							user.setPassword(rs.getString("password"));
							user.setEmail(rs.getString("email"));
							user.setSchool(rs.getString("school"));
							user.setGrade(rs.getInt("grade"));
							user.setDue(rs.getDate("due").toString());
							user.setOpenid(rs.getString("openid"));
							user.setType(rs.getInt("type"));
							user.setAssistid(rs.getString("assistid"));
							user.setName(rs.getString("name"));
							list.add(user);
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return list;
				}
			}, offset,rows);
		}
		//2,查出所有没有助理的用户记录数
		@Override
		public int findAllNoAssign(){
			String sql="select * from user where assistid is null and type<>0";
			return (Integer)DBUtil.query(sql, new ResultSetHanlder() {

				@Override
				public Object doHandler(ResultSet rs) {
					Integer total=0;
					try {
						while(rs.next()){
							total+=1;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return total;
				}
				
			}, null);
			
		}
	//3，给用户分配助理，更新assistid字段
	@Override
	public  void updateAssistid(User user) {
		String sql="update  user set assistid=? where uid=?";
		DBUtil.update(sql, user.getAssistid(),user.getUid());
		
	};
	//4,根据assidtid查某个assist管理的所有的user
		@Override
		public ArrayList<User> findByAssistid(String assistid,int offset,int rows) {
			String sql="select * from user where assistid=?  limit ?,?";
			return (ArrayList<User>)DBUtil.query(sql,  new ResultSetHanlder() {
				@Override
				public Object doHandler(ResultSet rs) {
					ArrayList<User> list=new ArrayList<User>();
					try {
						while(rs.next()){
							User user=new User();
							user.setUid(rs.getString("uid"));
							user.setPhone(rs.getString("phone"));
							user.setPassword(rs.getString("password"));
							user.setEmail(rs.getString("email"));
							user.setSchool(rs.getString("school"));
							user.setGrade(rs.getInt("grade"));
							user.setDue(String.valueOf(rs.getDate("due")));
							user.setOpenid(rs.getString("openid"));
							user.setType(rs.getInt("type"));
							user.setAssistid(rs.getString("assistid"));
							user.setName(rs.getString("name"));
							list.add(user);
						}
					} catch (SQLException e) {
						e.printStackTrace();
						return null;
					}
					return list;
				}
				
			}, assistid,offset,rows);
		}
		//5,根据assidtid查某个assist管理的所有的user条数
		@Override
		public int findByAssistid(String assistid) {
			String sql="select * from user where assistid=?";
			return (Integer)DBUtil.query(sql,  new ResultSetHanlder() {
				@Override
				public Object doHandler(ResultSet rs) {
					Integer total=0;
					try {
						while(rs.next()){
							total+=1;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return total;
				}
				
			}, assistid);
		}
		//6,所有会员列表（type!=0）
		@Override
		public ArrayList<User> findAll(int page,int rows){
			String sql="select * from user limit ?,?";
			return (ArrayList<User>)DBUtil.query(sql,  new ResultSetHanlder() {
				@Override
				public Object doHandler(ResultSet rs) {
					ArrayList<User> list=new ArrayList<User>();
					try {
						while(rs.next()){
							User user=new User();
							user.setUid(rs.getString("uid"));
							user.setPhone(rs.getString("phone"));
							user.setPassword(rs.getString("password"));
							user.setEmail(rs.getString("email"));
							user.setSchool(rs.getString("school"));
							user.setGrade(rs.getInt("grade"));
							user.setDue(String.valueOf(rs.getDate("due")));
							user.setOpenid(rs.getString("openid"));
							user.setType(rs.getInt("type"));
							user.setAssistid(rs.getString("assistid"));
							user.setName(rs.getString("name"));
							list.add(user);
						}
					} catch (SQLException e) {
						e.printStackTrace();
						return null;
					}
					return list;
				}
				
			}, page,rows);
		}
		//7，所有会员计数
		@Override
		public int findAll(){
			String sql="select * from user where type<>0";
			return (Integer)DBUtil.query(sql, new ResultSetHanlder() {

				@Override
				public Object doHandler(ResultSet rs) {
					Integer total=0;
					try {
						while(rs.next()){
							total+=1;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return total;
				}
				
			}, null);
			
		}
				
		//8,查ordinary用户
		@Override
		public ArrayList<User> findOrdinary(int page,int rows) {
			String sql="select * from user where type=1 limit ?,?;";
			return (ArrayList<User>)DBUtil.query(sql, new ResultSetHanlder() {

				@Override
				public Object doHandler(ResultSet rs) {
					ArrayList<User> list=new ArrayList<User>();
					try {
						while(rs.next()){
							User user=new User();
							user.setUid(rs.getString("uid"));
							user.setPhone(rs.getString("phone"));
							user.setPassword(rs.getString("password"));
							user.setEmail(rs.getString("email"));
							user.setSchool(rs.getString("school"));
							user.setGrade(rs.getInt("grade"));
							user.setDue(rs.getDate("due").toString());
							user.setOpenid(rs.getString("openid"));
							user.setType(rs.getInt("type"));
							user.setAssistid(rs.getString("assistid"));
							user.setName(rs.getString("name"));
							list.add(user);
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return list;
				}
				
			}, page,rows);
		}
		//9,ordinary用户计数
		@Override
		public int findOrdinary(){
			String sql="select * from user where type=1";
			return (Integer)DBUtil.query(sql, new ResultSetHanlder() {

				@Override
				public Object doHandler(ResultSet rs) {
					Integer total=0;
					try {
						while(rs.next()){
							total+=1;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return total;
				}
				
			}, null);
			
		}
	    //10,查vip用户
		@Override
		public ArrayList<User> findVip(int page,int rows) {
			String sql="select * from user where type=2 limit ?,?;";
			return (ArrayList<User>)DBUtil.query(sql, new ResultSetHanlder() {

				@Override
				public Object doHandler(ResultSet rs) {
					ArrayList<User> list=new ArrayList<User>();
					try {
						while(rs.next()){
							User user=new User();
							user.setUid(rs.getString("uid"));
							user.setPhone(rs.getString("phone"));
							user.setPassword(rs.getString("password"));
							user.setEmail(rs.getString("email"));
							user.setSchool(rs.getString("school"));
							user.setGrade(rs.getInt("grade"));
							user.setDue(rs.getDate("due").toString());
							user.setOpenid(rs.getString("openid"));
							user.setType(rs.getInt("type"));
							user.setAssistid(rs.getString("assistid"));
							user.setName(rs.getString("name"));
							list.add(user);
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return list;
				}
			}, page,rows);
		}
		//11.vip用户计数
		@Override
		public int findVip(){
			String sql="select * from user where type=2;";
			return (Integer)DBUtil.query(sql, new ResultSetHanlder() {

				@Override
				public Object doHandler(ResultSet rs) {
					Integer total=0;
					try {
						while(rs.next()){
							total+=1;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						return null;
					}
					return total;
				}
				
			}, null);
		}
	//12,参加某活动的人员名单
	@Override
	public ArrayList<User> findByTravelId(int travelid,int offset,int rows){
		String sql="select * from user where uid in (select uid from user_travel where travelid=?) limit ?,?;";
		return (ArrayList<User>)DBUtil.query(sql, new ResultSetHanlder() {

			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<User> list=new ArrayList<User>();
				try {
					while(rs.next()){
						User user=new User();
						user.setUid(rs.getString("uid"));
						user.setPhone(rs.getString("phone"));
						user.setPassword(rs.getString("password"));
						user.setEmail(rs.getString("email"));
						user.setSchool(rs.getString("school"));
						user.setGrade(rs.getInt("grade"));
						user.setDue(rs.getDate("due").toString());
						user.setOpenid(rs.getString("openid"));
						user.setType(rs.getInt("type"));
						user.setAssistid(rs.getString("assistid"));
						user.setName(rs.getString("name"));
						list.add(user);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, travelid,offset,rows);
		
		
	}
	//13，参加某活动的人员名单计数
	@Override
	public int findByTravelId(int travelid){
		String sql="select * from user where uid in (select uid from user_travel where travelid=?);";
		return (Integer)DBUtil.query(sql, new ResultSetHanlder() {

			@Override
			public Object doHandler(ResultSet rs) {
				Integer total=0;
				try {
					while(rs.next()){
						total+=1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return total;
			}
		}, travelid);
	}
	
    //注册用户，方式一:用phone注册，自动生成uid
	@Override
	public void addUser(User user) {
		String sql="insert into user(phone,uid)"
				+ " values(?,?)";
		DBUtil.update(sql, user.getPhone(),user.getUid());
	}
	//扫码注册
	@Override
	public void addUser2(User user) {
		String sql="insert into user(uid,password,openid)"
				+ " values(?,?,?)";
		DBUtil.update(sql,user.getUid(),user.getPassword(),user.getOpenid());
	}
	//微信端自动绑定
	@Override
	public void addUser3(User user) {
		String sql="insert into user(uid,img,openid,nickname,address)"
				+ " values(?,?,?,?,?)";
		DBUtil.update(sql,user.getUid(),user.getImg(),user.getOpenid(),user.getNickname(),user.getAddress());
	}
	//根据微信openid查询用户
	@Override
	public User findByOpenId(String openid) {
		String sql="select * from user where openid=?";
		return (User)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				User user=null;
				try {
					if(rs.next()){
						user=new User();
						user.setUid(rs.getString("uid"));
						user.setPhone(rs.getString("phone"));
						user.setPassword(rs.getString("password"));
						user.setEmail(rs.getString("email"));
						user.setSchool(rs.getString("school"));
						user.setGrade(rs.getInt("grade"));
// 						user.setDue(rs.getDate("due"));
					    user.setOpenid(rs.getString("openid"));
						user.setType(rs.getInt("type"));
						user.setAssistid(rs.getString("assistid"));
						user.setName(rs.getString("name"));
						user.setImg(rs.getString("img"));
						user.setNickname(rs.getString("nickname"));
						user.setMajor(rs.getString("major"));
						user.setAddress(rs.getString("address"));
					}
				} catch (SQLException e) {
					return null;
				}
				return user;
			}
		}, openid);
	}
	//根据phone查出用户
	@Override
	public User findByPhone(String phone){
		String sql="select * from user where phone=?";
		return (User)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				User user=null;
				try {
					if(rs.next()){
					  user=new User();
					  user.setPhone(rs.getString(2));
					}
				} catch (SQLException e) {
					return null;
				}
				return user;
			}
		}, phone);
		
	}
   //绑定微信号时，更新openid到数据库
	@Override
	public void update(User user) {
		String sql="update user set openid=? where phone=?";
		DBUtil.update(sql,user.getOpenid(),user.getPhone());
	}
	//填写资料加入会员时，更新字段
	@Override
	public void update2(User user) {
		String sql="update user set name=?,email=?,school=?,grade=?,type=1 where phone=?";
		DBUtil.update(sql,user.getName(),user.getEmail(),user.getSchool(),user.getGrade(),user.getPhone());
	}
    //给用户分配assist时，更新assistid字段
	@Override
	public void update3(User user) {
		String sql="update user set assistid=? where uid=?";
		DBUtil.update(sql, user.getAssistid(),user.getUid());
	}
	
	//根据uid查出用户，修改个人资料时使用
	@Override
	public User findByUid(String uid) {
		String sql="select * from user where uid=?";
		return (User)DBUtil.query(sql, new ResultSetHanlder() {

			@Override
			public Object doHandler(ResultSet rs) {
				User user=new User();
				try {
					if(rs.next()){
						user=new User();
						user.setUid(rs.getString("uid"));
						user.setPhone(rs.getString("phone"));
						user.setPassword(rs.getString("password"));
						user.setEmail(rs.getString("email"));
						user.setSchool(rs.getString("school"));
						user.setGrade(rs.getInt("grade"));
/*						user.setDue(rs.getDate("due"));
*/					    user.setOpenid(rs.getString("openid"));
						user.setType(rs.getInt("type"));
						user.setAssistid(rs.getString("assistid"));
						user.setName(rs.getString("name"));
						user.setImg(rs.getString("img"));
						user.setNickname(rs.getString("nickname"));
						user.setMajor(rs.getString("major"));
						user.setAddress(rs.getString("address"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return user;
			}
		}, uid);
		
	}
	//添加备份信息，更新backup字段
	@Override
	public void update4(User user){
		String sql="update user set backup=? where uid=?";
		DBUtil.update(sql, user.getBackup(),user.getUid());
	}
	//查出所有已经被分配了assist的用户
	@Override
	public ArrayList<User> findAssigned() {
		String sql="select * from user where assistid is not NULL and type<>0";
		return (ArrayList<User>)DBUtil.query(sql, new ResultSetHanlder() {

			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<User> list=new ArrayList<User>();
				try {
					while(rs.next()){
						User user=new User();
						user.setUid(rs.getString(1));
						user.setPhone(rs.getString(2));
						list.add(user);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
			
		}, null);
	}
	
	//phone+password登录时的校验查询
	@Override
	public User findByphonepwd(String phone,String pwd){
		String sql="select * from user where phone=? and password=?";
		return (User)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				User user=null;
				try {
					if(rs.next()){
						user=new User();
/*						user.setOpenid(rs.getString(1));
*/						user.setPhone(rs.getString(2));
			/*			user.setEmail(rs.getString(3));
						user.setSchool(rs.getString(4));
						user.setGrade(rs.getInt(5));
						user.setDue(rs.getDate(6).toGMTString());
						user.setPassword(rs.getString(7));*/
					}
				} catch (SQLException e) {
					return null;
				}
				return user;
			}
		}, phone,pwd);
		
	}
	
	
	

	
	/*
	

	@Override
	public ArrayList<User> getUserAll() {
		String sql="select * from user ";
		return (ArrayList<User>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<User> users=new ArrayList<User>();
				try {
					while(rs.next()){
						User user=new User();
						user.setOpenid(rs.getString(1));
						user.setPhonenumber(rs.getString(2));
						user.setEmail(rs.getString(4));
						user.setSchool(rs.getString(5));
						user.setGrade(rs.getInt(6));
						user.setDue(rs.getDate(7).toGMTString());
						users.add(user);
					}
				} catch (SQLException e) {
					return null;
				}
				return users;
			}
		}, null);
	}
	
    //验证用户是否合法,并返回User
	@Override
	public User checkUser(User user) {
		String sql="select * from user where user_id=?";
		return (User)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				try {
					if(!rs.next()){
						return null;
					}else{
						user.setName(rs.getString("user_name"));
						user.setEmail(rs.getString("email"));
						user.setOpenid(rs.getString("id"));
						user.setSchool(rs.getString("school"));
						user.setGrade(rs.getInt("grade"));
						user.setDue(rs.getDate("due").toGMTString());
						
						
					}
				} catch (SQLException e) {
					return null;

				}
				return user;
			}
		}, user.getOpenid());
	}

*/


	@Override
	public void update(String id) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public User findById(String uid) {
		String sql="select * from user where uid=?";
		return (User)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				User user=null;
				try {
					if(rs.next()){
						user=new User();
						user.setUid(rs.getString("uid"));
						user.setPhone(rs.getString("phone"));
						user.setPassword(rs.getString("password"));
						user.setEmail(rs.getString("email"));
						user.setSchool(rs.getString("school"));
						user.setGrade(rs.getInt("grade"));
						user.setDue(rs.getDate("due").toString());
						user.setOpenid(rs.getString("openid"));
						user.setType(rs.getInt("type"));
						user.setAssistid(rs.getString("assistid"));
						user.setName(rs.getString("name"));
						user.setImg(rs.getString("img"));
						user.setNickname(rs.getString("nickname"));
						user.setMajor(rs.getString("major"));
						user.setAddress(rs.getString("address"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return user;
			}
		}, uid);
	}
	//查找会员对应的文件 
	@Override
	public ArrayList<String[]> findFileByUid(String uid) {
		String sql="select * from user_files where uid=?";
		return (ArrayList<String[]>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<String[]> list=new ArrayList<String[]>();
				try {
					while(rs.next()){
						String[] array=new String[4];
						array[0]=String.valueOf(rs.getInt("id"));
						array[1]=rs.getString("uid");
						array[2]=rs.getString("url");
						array[3]=rs.getString("title");
						list.add(array);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, uid);
	}
	//给会员添加附件
	@Override
	public void addFile(String uid,String url,String title) {
		String sql="insert into user_files(uid,url,title) values(?,?,?)";
		DBUtil.update(sql, uid,url,title);
	}
	//删除会员附件
	@Override
	public void deleteFile(String uid, String url) {
		String sql="delete from user_files where uid=? and url=?";
		DBUtil.update(sql, uid,url);
		
	}
	//会员关联的任务
	@Override
	public ArrayList<Task> findTaskByUid(String uid) {
		String sql="select * from task where id in (select taskid from task_user where userid=?);";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						Task task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setResponsibility(rs.getString("responsibility"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, uid);
	}
	@Override
	public void addUserLink(String uid, String url) {
		String sql="insert into user_url values(?,?)";
		DBUtil.update(sql, uid,url);
	}
	@Override
	public void updateUserLink(String uid, String url) {
		String sql="update user_url set url=? where uid=?;";
		DBUtil.update(sql, url,uid);
	}
	@Override
	public String findLinkByUid(String uid) {
		String sql="select url from user_url where uid=?;";
		return (String)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				String str=null;
				try {
					if(rs.next()){
						str=new String();
						str=rs.getString("url");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return str;
			}
		}, uid);
	}
	@Override
	public ArrayList<Travel> findTravelsByUid(String uid) {
		String sql="select * from travel where id in (select travelid from user_travel where uid=?)";
		return (ArrayList<Travel>)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Travel> list=new ArrayList<Travel>();
				try {
					while(rs.next()){
						Travel t=new Travel();
						t.setId(rs.getInt("id"));
						t.setTitle(rs.getString("title"));
						t.setContent(rs.getString("content"));
						t.setDatetime(rs.getDate("datetime"));
						t.setFirstimgurl(rs.getString("firstimgurl"));
						t.setMoney(rs.getDouble("money"));
						list.add(t);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, uid);
	}
	@Override
	public Travel findTravelById(int id) {
		String sql="select * from travel where id=?";
		return (Travel)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				Travel t=null;
				try {
					while(rs.next()){
						t=new Travel();
						t.setId(rs.getInt("id"));
						t.setTitle(rs.getString("title"));
						t.setContent(rs.getString("content"));
						t.setDatetime(rs.getDate("datetime"));
						t.setFirstimgurl(rs.getString("firstimgurl"));
						t.setMoney(rs.getDouble("money"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return t;
			}
		}, id);
	}
	//修改会员资料
	@Override
	public void updateUserInfo(User user) {
		String sql="update user set name=?,phone=?,email=?,school=?,grade=?,major=?,address=? where uid=?";
		DBUtil.update(sql, user.getName(),user.getPhone(),user.getEmail(),user.getSchool(),user.getGrade(),user.getMajor(),user.getAddress(),user.getUid());
	}
	//管理员手动添加会员
	@Override
	public void addUserByAdminer(User user) {
		String sql="insert into user(uid,phone,email,school,grade,due,type,assistid,name) values(?,?,?,?,?,?,?,?,?)";
		DBUtil.update(sql, user.getUid(),user.getPhone(),user.getEmail(),user.getSchool(),user.getGrade(),user.getDue(),user.getType(),user.getAssistid(),user.getName());
	}
	//管理员给用户添加定时消息
	@Override
	public TimerMessage addComment(TimerMessage tmsg) {
		String sql="insert into backupforuser(content,uid,assistid,isread,availableopen) values(?,?,?,0,0)";
		String sql2="select last_insert_id()";
		String sql3="select * from backupforuser where id=?";
		Connection conn=DBUtil.getConnetion();
		PreparedStatement ps=null;
		TimerMessage tm=null;
		ResultSet rs=null;
		int id=0;
		try {
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			ps.setString(1, tmsg.getContent());
			ps.setString(2, tmsg.getUid());
			ps.setString(3, tmsg.getAssistid());
			ps.execute();
			ps=conn.prepareStatement(sql2);
			rs=ps.executeQuery();
			if(rs.next()){
				id=rs.getInt(1);
			}
			System.out.println("id-----"+id);
			ps=conn.prepareStatement(sql3);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()){
				tm=new TimerMessage();
				tm.setId(rs.getInt("id"));
				tm.setAssistid(rs.getString("assistid"));
				tm.setContent(rs.getString("content"));
				tm.setDatetime(rs.getDate("datetime"));
				tm.setUid(rs.getString("uid"));
			}
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
			DBUtil.RelaseSource(rs, ps, conn);
		}
		return tm;

	}
	//某个管理员的某个会员的comments
	@Override
	public ArrayList<TimerMessage> findCommentByassist(String assistid,String uid) {
		String sql="select * from backupforuser where assistid=? and uid=? order by id desc";
		return (ArrayList<TimerMessage>)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<TimerMessage> list=new ArrayList<TimerMessage>();
				try {
					while(rs.next()){
						TimerMessage tm=new TimerMessage();
						tm.setAssistid(rs.getString("assistid"));
						tm.setContent(rs.getString("content"));
						tm.setDatetime(rs.getDate("datetime"));
						tm.setId(rs.getInt("id"));
						tm.setUid(rs.getString("uid"));
					    list.add(tm);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, assistid,uid);

	}
	@Override
	public void deleteComment(int id) {
		String sql="delete from backupforuser where id=?";
		DBUtil.update(sql, id);
	}
	@Override
	public void editComment(int id, String content) {
		String sql="update backupforuser set content=? where id=?";
		DBUtil.update(sql, content,id);
	}
	@Override
	public void setDateTime(int id, Date datetime) {
		String sql="update backupforuser set datetime=?,availableopen=1 where id=?";
		DBUtil.update(sql, datetime,id);
	}
	@Override
	public ArrayList<TimerMessage> findTimerMessage(String assistid) {
		String sql="select * from backupforuser where assistid=? and availableopen=1";
		return (ArrayList<TimerMessage>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<TimerMessage> list=new ArrayList<TimerMessage>();
				try {
					while(rs.next()){
						TimerMessage tm=new TimerMessage();
						tm.setId(rs.getInt("id"));
						tm.setUid(rs.getString("uid"));
						tm.setDatetime(rs.getDate("datetime"));
						tm.setAssistid(rs.getString("assistid"));
						tm.setContent(rs.getString("content"));
						list.add(tm);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, assistid);
	}
	@Override
	public void closeTimerAfterShow(int id) {
		String sql="update backupforuser set availableopen=0 where id=?";
		DBUtil.update(sql, id);
		
	}
	@Override
	public TimerMessage findTimerMessage(int id) {
		String sql="select * from backupfor where id=?";
		return (TimerMessage)DBUtil.query(sql, new ResultSetHanlder() {
						
					@Override
					public Object doHandler(ResultSet rs) {
						TimerMessage tm=null;
						try {
							if(rs.next()){
							    tm=new TimerMessage();
								tm.setId(rs.getInt("id"));
								tm.setUid(rs.getString("uid"));
								tm.setDatetime(rs.getDate("datetime"));
								tm.setAssistid(rs.getString("assistid"));
								tm.setContent(rs.getString("content"));
							}
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							return null;
						}
						return tm;
					}
				}, id);
			}
	@Override
	public void setUserType(String openid,int type) {
		String sql="update user set type=? where openid=?";
		DBUtil.update(sql,type,openid);
	}
}
